import time,re
import pandas as pd
import sys
import redis
import pickle
import logging
import hashlib
import configparser
import datetime
from sqlalchemy import create_engine, DateTime, String,text
import pymysql
import requests
import os
import pywencai
import akshare as ak
import pandas as pd
from datetime import datetime,timedelta
import traceback
from tools import Tools

pymysql.install_as_MySQLdb()

os.environ['PATH'] = '/home/chencan/node/bin:$PATH";'
log_format = "%(asctime)s - %(levelname)s - %(filename)s:%(lineno)d - %(message)s"
date_format = "%Y-%m-%d %H:%M:%S"  # 精确到秒
logging.basicConfig(level=logging.DEBUG, format=log_format, datefmt=date_format)

# 日志文件路径
log_file_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), 'application.log')

# 创建一个 handler，用于写入日志文件
file_handler = logging.FileHandler(log_file_path)
file_handler.setFormatter(logging.Formatter(log_format, date_format))
# 添加 handler 到 logger
logging.getLogger().addHandler(file_handler)

# 初始化配置解析器
config = configparser.ConfigParser()
current_dir = os.path.dirname(os.path.abspath(__file__))
config.read(current_dir+'/config.ini', encoding='utf-8')


# 获取Redis的配置信息
redis_host = config.get('Redis', 'host')
redis_port = config.getint('Redis', 'port')
redis_db = config.getint('Redis', 'db')
redis_password = config.get('Redis', 'password')
r = redis.Redis(host=redis_host, port=redis_port, db=redis_db, password=redis_password)

mysql_port = config.getint('mysql', 'port')
mysql_host = config.get('mysql', 'host')
mysql_db = config.get('mysql', 'db')
import urllib.parse
mysql_password = urllib.parse.quote(config.get('mysql', 'password'))
mysql_user = config.get('mysql', 'user')
db_url = f'mysql://{mysql_user}:{mysql_password}@{mysql_host}:{mysql_port}/{mysql_db}'

engine = create_engine(db_url,pool_size=20,max_overflow=20,pool_recycle=60)



def get_next_trade_date(trade_date):
    tool_trade_date_hist_sina_df = ak.tool_trade_date_hist_sina()
    # 筛选出所有晚于给定交易日的日期
    date_df = tool_trade_date_hist_sina_df[tool_trade_date_hist_sina_df["trade_date"] > pd.Timestamp(trade_date).date()]
    date_df = date_df.sort_values(by="trade_date", ascending=True)  # 按日期升序排序
    next_trade_date = date_df["trade_date"].values[0]  # 获取最接近给定日期的下一个交易日
    return next_trade_date.strftime("%Y%m%d")  # 格式化日期

# 定义获取前一个交易日的函数
def get_pre_trade_date(trade_date):
    tool_trade_date_hist_sina_df = ak.tool_trade_date_hist_sina()
    date_df = tool_trade_date_hist_sina_df[tool_trade_date_hist_sina_df["trade_date"] < pd.Timestamp(trade_date).date()]
    date_df = date_df.sort_values(by="trade_date", ascending=False)
    pre_trade_date = date_df["trade_date"].values[0]
    return pre_trade_date.strftime("%Y%m%d")

def get_pre_trade_date_n(trade_date,n):
    tool_trade_date_hist_sina_df = ak.tool_trade_date_hist_sina()
    date_df = tool_trade_date_hist_sina_df[tool_trade_date_hist_sina_df["trade_date"] < pd.Timestamp(trade_date).date()]
    date_df = date_df.sort_values(by="trade_date", ascending=False)
    pre_trade_date = date_df["trade_date"].values[n-1]
    return pre_trade_date.strftime("%Y%m%d")

def get_trading_days(start_date, end_date):
        """
        计算两个日期之间的交易日。
        :param start_date: 开始日期，格式为 'YYYYMMDD'
        :param end_date: 结束日期，格式为 'YYYYMMDD'
        :param trade_dates: 交易日列表
        :return: 交易日数量
        """
        # 将日期字符串转换为 datetime 对象
        start_dt = datetime.strptime(start_date, '%Y%m%d').date()
        end_dt = datetime.strptime(end_date, '%Y%m%d').date()
        trade_dates = ak.tool_trade_date_hist_sina()
        trade_dates = trade_dates['trade_date'].tolist()

        # 过滤出在指定日期范围内的交易日
        filtered_trade_dates = [date for date in trade_dates if start_dt <= date <= end_dt]
        formatted_dates = [date.strftime('%Y%m%d') for date in filtered_trade_dates]
        # 返回交易日数量
        return formatted_dates

#记录前一天的数据
def getRps(query_date):
    pre_trade_date = get_pre_trade_date(query_date)
    next_trade_date = get_next_trade_date(query_date)
    last_month_date_20 = get_pre_trade_date_n(query_date,20)
    last_month_date_5 = get_pre_trade_date_n(query_date, 5)


    question =f'''
        {last_month_date_20}到{pre_trade_date}的区间涨跌幅，{last_month_date_5}到{pre_trade_date}的区间涨跌幅，上市时间大于30天，非北交所，非ST，
    '''

    logging.info(f"问句:{question}")

    # proxy = Tools.get_http_proxy()
    #
    # proxies = {
    #     'http': f'http://{proxy}'
    # }
    #
    # request_params = {'proxies': proxies, 'timeout':5 }
    # print(request_params)


    # df = pywencai.get(query=question,loop=True,sleep=0.1,request_params=request_params)
    df = pywencai.get(query=question, loop=True, sleep=0.5)
    logging.info(f"获得的列名:{df.columns}")
    df["股票代码"] = df["股票代码"].str[0:6]

    df = df.rename(columns={f'区间涨跌幅:前复权[{last_month_date_20}-{pre_trade_date}]': '20日涨跌幅'})
    df = df.rename(columns={f'区间涨跌幅:前复权[{last_month_date_5}-{pre_trade_date}]': '5日涨跌幅'})

    df["20日涨跌幅"] = round(df["20日涨跌幅"].astype(float),2)
    df["5日涨跌幅"] = round(df["5日涨跌幅"].astype(float),2)

    df = df[["股票代码","股票简称","20日涨跌幅","5日涨跌幅"]]
    df['rps20'] = df['20日涨跌幅'].rank(ascending=False, method="dense")
    df['rps20'] = round((1-df['rps20']/len(df))*100,2)

    df['rps5'] = df['5日涨跌幅'].rank(ascending=False, method="dense")
    df['rps5'] = round((1-df['rps5']/len(df))*100,2)


    df['trade_date'] = query_date

    with engine.connect() as connection:
        sql = f"DELETE FROM stock_rps WHERE trade_date='{query_date}'"
        print(sql)
        delete_statement = text(sql)
        result = connection.execute(delete_statement)
        connection.commit()
        print(result.rowcount, "rows deleted.")
        df.to_sql("stock_rps", engine, if_exists='append', index=False)

    print(df)


    return df

def stock_rps(query_date):
    pre_20_day = get_pre_trade_date_n(query_date,20)
    sql = f'''
        SELECT 股票简称, MAX(`rps20`) AS rps
        FROM stock_rps
        WHERE trade_date >= '{pre_20_day}' and trade_date<='{query_date}'
        GROUP BY 股票简称;
        '''
    df_rps = pd.read_sql(sql, engine)
    # stock_rps_list = df_rps["股票简称"].values
    r.set("stock_rps",pickle.dumps(df_rps))




if __name__ == '__main__':
    # 检查是否有命令行参数（除了脚本名称本身）
    # date_df = get_trading_days("20240301","20240401")
    # for v in date_df:
    #     getRps(v)
    query_date = datetime.now().strftime('%Y%m%d')
    getRps(query_date)
    stock_rps(query_date)








